package com.bookmall.dao.impl;

import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;

import com.bookmall.dao.BookDao;
import com.bookmall.entity.Book;
import com.bookmall.util.DBUtil;

public class BookDaoImpl implements BookDao {

	@Override
	public boolean insert(Book t) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = "insert into book_tb(bookname,info,author,publish,salecount,typeid,price) values(?,?,?,?,?,?,?)";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, t.getBookName());
		ps.setString(2, t.getInfo());
		ps.setString(3, t.getAuthor());
		ps.setString(4, t.getPublish());
		ps.setInt(5, t.getSaleCount());
		ps.setInt(6, t.getTypeId());
		ps.setDouble(7, t.getPrice());
		int row = ps.executeUpdate();
		DBUtil.closeConnection(c, ps, null);
		return row == 1 ? true : false;
	}

	@Override
	public boolean update(Book t) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = "update book_tb set bookname=?,info=?,author=?,publish=?,price=?,"
				+ " salecount=?,typeid=? where bookid = ? ";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, t.getBookName());
		ps.setString(2, t.getInfo());
		ps.setString(3, t.getAuthor());
		ps.setString(4, t.getPublish());
		ps.setDouble(5, t.getPrice());
		ps.setInt(6, t.getSaleCount());
		ps.setInt(7, t.getTypeId());
		ps.setInt(8, t.getBookId());
		int row = ps.executeUpdate();
		DBUtil.closeConnection(c, ps, null);
		return row == 1 ? true : false;
	}

	@Override
	public boolean delete(int id) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = "update book_tb set delete_flag = true where bookid = ?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, id);
		int rs = ps.executeUpdate();
		DBUtil.closeConnection(c, ps, null);
		return rs == 1 ? true : false;
	}

	@Override
	public Book selectById(int id) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = "select bookid,bookname,info,author,publish,price,salecount,typeid "
				+ "from book_tb where bookid = ?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			Book b = new Book();
			b.setBookId(rs.getInt(1));
			b.setBookName(rs.getString(2));
			b.setInfo(rs.getString(3));
			b.setAuthor(rs.getString(4));
			b.setPublish(rs.getString(5));
			b.setPrice(rs.getDouble(6));
			b.setSaleCount(rs.getInt(7));
			b.setTypeId(rs.getInt(8));
			DBUtil.closeConnection(c, ps, rs);
			return b;
		} else {
			DBUtil.closeConnection(c, ps, rs);
			return null;
		}
	}

	@Override
	public List<Book> selectByPage(int page, int pageSize) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = "select bookid,bookname,info,author,publish,price,salecount,typeid " + "from book_tb limit ?,?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, (page - 1) * pageSize);
		ps.setInt(2, pageSize);
		ResultSet rs = ps.executeQuery();

		List<Book> books = new LinkedList<>();

		while (rs.next()) {
			Book b = new Book();
			b.setBookId(rs.getInt(1));
			b.setBookName(rs.getString(2));
			b.setInfo(rs.getString(3));
			b.setAuthor(rs.getString(4));
			b.setPublish(rs.getString(5));
			b.setPrice(rs.getDouble(6));
			b.setSaleCount(rs.getInt(7));
			b.setTypeId(rs.getInt(8));
			books.add(b);
		}
		DBUtil.closeConnection(c, ps, rs);
		return books.isEmpty() ? Collections.emptyList() : books;
	}

	@Override
	public List<Book> selectByIds(List<Integer> ids, int page, int pageSize) throws SQLException {
		// TODO Auto-generated method stub
		Connection c = DBUtil.getConnection();
		String sql = "select bookid,bookname,info,author,publish,price,salecount,create_time,modify_time,typeid from book_tb where bookid in (?) and delete_flag = false limit ?,?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setArray(1, (Array) ids);
		ps.setInt(2, (pageSize - 1) * page);
		ps.setInt(3, pageSize);
		ResultSet rs = ps.executeQuery();
		List<Book> books = new LinkedList<>();
		while (rs.next()) {
			Book b = new Book();
			b.setBookId(rs.getInt(1));
			b.setBookName(rs.getString(2));
			b.setInfo(rs.getString(3));
			b.setAuthor(rs.getString(4));
			b.setPublish(rs.getString(5));
			b.setPrice(rs.getDouble(6));
			b.setSaleCount(rs.getInt(7));
			b.setTypeId(rs.getInt(8));
			books.add(b);
		}
		DBUtil.closeConnection(c, ps, null);
		return books.isEmpty() ? Collections.emptyList() : books;
	}

	@Override
	public int deleteByIds(List<Integer> ids) throws SQLException {
		// TODO Auto-generated method stub
		Connection c = DBUtil.getConnection();
		String sql = "update book_tb set delete_flag = ? where bookid in (?)";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setArray(1, (Array) ids);
		ResultSet rs = ps.executeQuery();
		int row = rs.getRow();
		DBUtil.closeConnection(c, ps, null);
		return row;
	}

	@Override
	public List<Book> selectByName(String bookName, int page, int pageSize) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = "select bookid,bookname,info,author,publish,price,salecount,create_time,modify_time,typeid from book_tb where bookname = ? and delete_flag = false limit ?,?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, bookName);
		ps.setInt(2, (pageSize - 1) * page);
		ps.setInt(3, pageSize);
		ResultSet rs = ps.executeQuery();
		List<Book> books = new LinkedList<>();
		while (rs.next()) {
			Book b = new Book();
			b.setBookId(rs.getInt(1));
			b.setBookName(rs.getString(2));
			b.setInfo(rs.getString(3));
			b.setAuthor(rs.getString(4));
			b.setPublish(rs.getString(5));
			b.setPrice(rs.getDouble(6));
			b.setSaleCount(rs.getInt(7));
			b.setTypeId(rs.getInt(8));
			books.add(b);
		}
		DBUtil.closeConnection(c, ps, null);
		return books.isEmpty() ? Collections.emptyList() : books;
	}

	@Override
	public List<Book> selectByPrice(double lowPrice, double hiPrice, int page, int pageSize) throws SQLException {
		// TODO Auto-generated method stub
		Connection c = DBUtil.getConnection();
		String sql = "select bookid,bookname,info,author,publish,price,salecount,create_time,modify_time,typeid from book_tb where price between ? and ? and delete_flag = false limit ?,?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setDouble(1, lowPrice);
		ps.setDouble(2, hiPrice);
		ps.setInt(3, (pageSize - 1) * page);
		ps.setInt(4, pageSize);
		ResultSet rs = ps.executeQuery();
		List<Book> books = new LinkedList<>();
		while (rs.next()) {
			Book b = new Book();
			b.setBookId(rs.getInt(1));
			b.setBookName(rs.getString(2));
			b.setInfo(rs.getString(3));
			b.setAuthor(rs.getString(4));
			b.setPublish(rs.getString(5));
			b.setPrice(rs.getDouble(6));
			b.setSaleCount(rs.getInt(7));
			b.setTypeId(rs.getInt(8));
			books.add(b);
		}
		DBUtil.closeConnection(c, ps, null);
		return books.isEmpty() ? Collections.emptyList() : books;
	}

	@Override
	public List<Book> selectByAuthor(String author, int page, int pageSize) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = "select bookid,bookname,info,author,publish,price,salecount,create_time,modify_time,typeid from book_tb where delete_flag = false and author = ?  limit ?,?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, author);
		ps.setInt(2, (pageSize - 1) * page);
		ps.setInt(3, pageSize);
		ResultSet rs = ps.executeQuery();
		List<Book> books = new LinkedList<>();
		while (rs.next()) {
			Book b = new Book();
			b.setBookId(rs.getInt(1));
			books.add(b);
		}
		DBUtil.closeConnection(c, ps, null);
		return books.isEmpty() ? Collections.emptyList() : books;

	}

	@Override
	public List<Book> selectByTypeId(int typeId, int page, int pageSize) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = "select bookid,bookname,info,author,publish,price,salecount,typeid " + "from book_tb limit ?,?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, (page - 1) * pageSize);
		ps.setInt(2, pageSize);
		ResultSet rs = ps.executeQuery();

		List<Book> books = new LinkedList<>();

		while (rs.next()) {
			Book b = new Book();
			b.setBookId(rs.getInt(1));
			b.setBookName(rs.getString(2));
			b.setInfo(rs.getString(3));
			b.setAuthor(rs.getString(4));
			b.setPublish(rs.getString(5));
			b.setPrice(rs.getDouble(6));
			b.setSaleCount(rs.getInt(7));
			b.setTypeId(rs.getInt(8));
			books.add(b);
		}
		DBUtil.closeConnection(c, ps, rs);
		return books.isEmpty() ? Collections.emptyList() : books;
	}

	@Override
	public int updateBookImg(int bookId, String img) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = "update book_tb set img = ? where bookId = ? and delete_flag = false";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, img);
		ps.setInt(2, bookId);
		int row = ps.executeUpdate();
		DBUtil.closeConnection(c, ps, null);
		return row;
	}

	@Override
	public int updateSaleCount(int bookId, int addCount) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = "update book_tb set salecount = salecount + ? where bookId = ?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, addCount);
		ps.setInt(2, bookId);
		int row = ps.executeUpdate();
		DBUtil.closeConnection(c, ps, null);
		return row;
	}

}
